 /**
   * Copyright 2014 Viettel Telecom. All rights reserved.
   * VIETTEL PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
   */
 package com.viettel.core.dao.cms; 
import java.util.ArrayList;
import java.util.List;

import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;

import com.viettel.core.common.utils.StringUtil;
import com.viettel.core.entities.cms.AssignQuestion;
import com.viettel.core.entities.cms.CmsUser;
import com.viettel.core.entities.cms.Questions;
import com.viettel.core.entities.vo.AssignQuestionSearchFilter;
import com.viettel.core.entities.vo.AssignQuestionSearchVO;
import com.viettel.core.enumtype.AssignQuestionStatus;
import com.viettel.core.enumtype.QuestionsStatus;
import com.viettel.core.exceptions.DataAccessException;
import com.viettel.core.repo.IRepository;
 /**
   *  Mo ta muc dich cua lop (interface)
   *  @author: TOANTM1
   *  @version:  
   *  @since: 27/02/2014 - 11:34:02
   */
 public class AssignQuestionDAOImpl implements AssignQuestionDAO { 
	 private IRepository repo; 
		
	 	public void setRepo(IRepository repo) {
			this.repo = repo;
		} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public AssignQuestion getAssignQuestionById(Integer id) throws DataAccessException { 
		return repo.getEntityById(AssignQuestion.class, id); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public AssignQuestion createAssignQuestion(AssignQuestion AssignQuestion) 
			throws DataAccessException { 
		if (AssignQuestion == null) { 
			throw new IllegalArgumentException("AssignQuestion"); 
		} 
		repo.create(AssignQuestion); 
		return repo.getEntityById(AssignQuestion.class, AssignQuestion.getId()); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void updateAssignQuestion(AssignQuestion AssignQuestion) throws DataAccessException { 
		if (AssignQuestion == null) { 
			throw new IllegalArgumentException("AssignQuestion"); 
		} 
		repo.update(AssignQuestion); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void deleteAssignQuestion(AssignQuestion AssignQuestion) throws DataAccessException { 
		if (AssignQuestion == null) { 
			throw new IllegalArgumentException("AssignQuestion"); 
		} 
		repo.delete(AssignQuestion); 
	}

	@Override
	public List<AssignQuestionSearchVO> searchAssignQuestionByFilter(
			AssignQuestionSearchFilter filter) throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();

		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
			
		selectSql.append("SELECT aq.id              AS id, ");
		selectSql.append("       q.id       	    AS questionId, ");
		selectSql.append("       q.content       	AS contentQuestion, ");
		selectSql.append("       us.phone       	AS phone, ");
		selectSql.append("       us.name       		AS name, ");
		selectSql.append("       cmsto.id       	AS assignToId, ");
		selectSql.append("       cmsto.username  	AS assignToName, ");
		selectSql.append("       cmsfrom.id       	AS assignFromId, ");
		selectSql.append("       cmsfrom.username  	AS assignFromName, ");
		selectSql.append("       aq.status          AS status, ");
		selectSql.append("       aq.created_Time    AS createdTime, ");
		selectSql.append("       aq.created_Time    AS updatedTime ");
		
		fromQuery.append("FROM ASSIGN_QUESTION aq ");
		fromQuery.append("		LEFT JOIN QUESTIONS q ");
		fromQuery.append("			ON aq.question_id = q.id ");
		fromQuery.append("		LEFT JOIN USERS us ");
		fromQuery.append("			ON q.user_id = us.id ");
		fromQuery.append("		LEFT JOIN CMS_USER cmsto ");
		fromQuery.append("			ON aq.assign_to = cmsto.id ");
		fromQuery.append("		LEFT JOIN CMS_USER cmsfrom ");
		fromQuery.append("			ON aq.assign_from = cmsfrom.id ");
		
		fromQuery.append("WHERE aq.status != ? ");
		paramSelect.add(AssignQuestionStatus.DELETED.getValue());
		countParams.add(AssignQuestionStatus.DELETED.getValue());
		
		fromQuery.append("AND q.status != ? ");
		paramSelect.add(QuestionsStatus.DELETED.getValue());
		countParams.add(QuestionsStatus.DELETED.getValue());
		
		if(filter.getStatus()!=null){
			fromQuery.append("AND q.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}
		
		
		if (!StringUtil.isNullOrEmpty(filter.getContentQuestion())) {
			fromQuery.append(" AND Upper(q.content_text) LIKE Upper(?) ESCAPE '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getContentQuestion()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getContentQuestion()) + "%");
		}

		if(filter.getFromDate()!=null && filter.getToDate()!=null ){
			fromQuery.append("  AND TRUNC(aq.CREATED_TIME) BETWEEN TRUNC(?) AND TRUNC(?) ");
			paramSelect.add(filter.getFromDate());
			countParams.add(filter.getFromDate());
			
			paramSelect.add(filter.getToDate());
			countParams.add(filter.getToDate());
		}
		/**
		 * toantm1
		 */
		if(filter.getAssignToId()!=null){
			fromQuery.append(" AND aq.assign_to=? ");
			paramSelect.add(filter.getAssignToId());
			countParams.add(filter.getAssignToId());
		}
		
		selectSql.append(fromQuery).append(" ORDER BY aq.created_time DESC ");
		countQuery.append("SELECT COUNT(*) AS count ").append(fromQuery);
		String[] fieldNames = new String[] { 
				"id", "contentQuestion",
				"assignToId", "assignToName", 
				"assignFromId", "assignFromName", 
				"status", "questionId",
				"createdTime" ,"updatedTime",
				"name","phone"};
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER,  StandardBasicTypes.INTEGER,
				StandardBasicTypes.TIMESTAMP, StandardBasicTypes.TIMESTAMP,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(AssignQuestion.class);
		synchronizedClass.add(CmsUser.class);
		synchronizedClass.add(Questions.class);
		
		List<AssignQuestionSearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					AssignQuestionSearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(AssignQuestionSearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}
		return list;
	} 
	
	@Override
	public List<AssignQuestion> getAssignQuestionByQuestionId(Integer questionId)
			throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT * FROM ASSIGN_QUESTION where status != ? AND question_id = ? ORDER BY id ";
		
		params.add(AssignQuestionStatus.DELETED.getValue());
		params.add(questionId);
		
		return repo.getListBySQL(AssignQuestion.class, sql, params);
	}
	
	@Override
	public List<AssignQuestion> getAssignQuestionByCmsUserId(Integer cmsUserId)
			throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT * FROM ASSIGN_QUESTION where status != ? AND assign_to = ? ORDER BY updated_time DESC ";
		
		params.add(AssignQuestionStatus.DELETED.getValue());
		params.add(cmsUserId);
		
		return repo.getListBySQL(AssignQuestion.class, sql, params);
	}
} 
 
